In [3]:
from pyspark import SparkContext, SparkConf
from pyspark.sql import SparkSession
from google.cloud import storage
from pyspark.sql.functions import lit
from functools import reduce
from pyspark.sql import DataFrame
from pyspark.sql.functions import *
from datetime import datetime
from pyspark.ml import Pipeline
from pyspark.sql.window import Window

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
In [4]:
spark = SparkSession.builder.appName('Nifty50').getOrCreate()

#change configuration settings on Spark 
/gateway/default/node/conf?host&port = spark.sparkContext._conf.setAll([('spark.executor.memory', '30g'), ('spark.app.name', 'Spark Updated Conf'), ('spark.executor.cores', '4'), ('spark.cores.max', '4'), ('spark.driver.memory','30g'), ("spark.driver.maxResultSize", "70g")])

gcs_client = storage.Client()
In [5]:
bucket_name = 'bigdata_nifty50'
In [6]:
bucket = gcs_client.bucket(bucket_name)
In [7]:
file_name = 'df_with_indicators.csv'
In [8]:
df = spark.read.csv('gs://{}//{}'.format(bucket_name, file_name), inferSchema=True)
In [9]:
new_columns = ['date',
 'close',
 'high',
 'low',
 'open',
 'volume',
 'sma5',
 'sma10',
 'sma15',
 'sma20',
 'ema5',
 'ema10',
 'ema15',
 'ema20',
 'upperband',
 'middleband',
 'lowerband',
 'HT_TRENDLINE',
 'KAMA10',
 'KAMA20',
 'KAMA30',
 'SAR',
 'TRIMA5',
 'TRIMA10',
 'TRIMA20',
 'ADX5',
 'ADX10',
 'ADX20',
 'APO',
 'CCI5',
 'CCI10',
 'CCI15',
 'macd510',
 'macd520',
 'macd1020',
 'macd1520',
 'macd1226',
 'MFI',
 'MOM10',
 'MOM15',
 'MOM20',
 'ROC5',
 'ROC10',
 'ROC20',
 'PPO',
 'RSI14',
 'RSI8',
 'slowk',
 'slowd',
 'fastk',
 'fastd',
 'fastksr',
 'fastdsr',
 'ULTOSC',
 'WILLR',
 'ATR',
 'Trange',
 'TYPPRICE',
 'HT_DCPERIOD',
 'BETA',
 'sector',
 'company',
 'Inflation Rate ',
 'Balance of Trade',
 'Bank Lending Rate',
 'Car Registrations',
 'Consumer Price Index',
 'Crude Oil Production',
 'Fiscal Expenditure',
 'Industrial Production',
 'Food Inflation',
 'Producer Prices',
 'Reverse Repo Rate',
 'Steel Production',
 'Tourist Arrivals',
 'Corporate Tax Rate ',
 'Export Prices',
 'GDP per Capita PPP',
 'GDP',
 'Gross National Product',
 'Import Prices',
 'Military Expenditure']
In [10]:
from functools import reduce

old_columns = df.schema.names
df = reduce(lambda data, idx: data.withColumnRenamed(old_columns[idx], new_columns[idx]), range(len(old_columns)), df)
In [11]:
from pyspark.sql.types import DateType
def get_month(date):
    return datetime.strptime('{}-{}'.format(date.year, date.month), '%Y-%m')
udf_get_month = udf(get_month, DateType())

df = df.withColumn("month", udf_get_month("date"))
cols = cols = df.columns
cols.remove('month')

exprs = {x: "avg" for x in cols}
exprs["sector"] = "first"

monthly_df = df.groupby(df.company, df.month).agg(exprs)
In [ ]:
monthly_df.show()

22/11/27 04:41:18 WARN org.apache.spark.sql.catalyst.util.package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.
[Stage 5:>                                                          (0 + 1) / 1]

+--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+
| company|     month|avg(Reverse Repo Rate)|        avg(RSI14)|avg(Car Registrations)|          avg(CCI10)|      avg(TRIMA10)|          avg(SAR)|      avg(fastdsr)|        avg(ADX20)|         avg(WILLR)|        avg(sma10)|       avg(macd1226)|        avg(sma20)|        avg(ema15)|        avg(ema10)|       avg(KAMA10)|      avg(TRIMA20)|avg(Tourist Arrivals)|      avg(fastksr)|avg(Steel Production)|avg(Gross National Product)|       avg(TRIMA5)|       avg(macd1020)|           avg(CCI5)|          avg(MOM15)|avg(Export Prices)|     avg(TYPPRICE)|avg(GDP per Capita PPP)|  avg(HT_DCPERIOD)|         avg(ema5)|       first(sector)|         avg(open)|avg(Producer Prices)|            avg(PPO)|        avg(ADX10)|         avg(CCI15)|avg(Fiscal Expenditure)|avg(company)|avg(Import Prices)|           avg(ATR)|avg(Crude Oil Production)|    avg(upperband)|          avg(low)|          avg(BETA)|          avg(ROC20)|        avg(macd520)|           avg(date)|          avg(MOM10)|        avg(fastk)|          avg(MFI)|avg(Corporate Tax Rate )|            avg(APO)|         avg(sma5)|       avg(KAMA20)|         avg(RSI8)|        avg(macd510)|    avg(lowerband)|avg(Military Expenditure)|        avg(sma15)|        avg(fastd)|         avg(ADX5)|        avg(slowd)|        avg(ema20)|avg(Consumer Price Index)|avg(Industrial Production)|       avg(KAMA30)|          avg(GDP)|avg(Bank Lending Rate)|        avg(Trange)|          avg(MOM20)|avg(Balance of Trade)| avg(HT_TRENDLINE)|       avg(ULTOSC)|avg(Inflation Rate )|           avg(ROC5)|        avg(close)|avg(Food Inflation)|   avg(middleband)|          avg(ROC10)|       avg(volume)|       avg(macd1520)|         avg(high)|        avg(slowk)|
+--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+
|     ACC|2015-02-01|                  6.75|51.341732681694985|              225854.0|   7.375884452695391|1587.8424552911074|1510.2454148176594|50.706838008648184|20.462641615692533| -46.29977340716897|  1587.84245999731| 0.12230880288822872|1587.7528822105724|1587.7986367188514| 1587.843024086454| 1587.733592170198|1587.7521971835392|             761000.0| 50.70507114232472|               7201.0|                1.1234571E7| 1587.888222552665| 0.08816524997553743|   4.343301891589986| 0.27004168347451923|108.59999999998561|1587.9311572766767|      5464.390000000442| 21.87608712444841|1587.8882100745654|Construction Mate...|1587.9137891622913|  109.59999999998553|0.007674402115951372| 29.21212900844431|  9.028998534078514|              1459536.0|        null| 99.80000000000787| 1.8585889236980633|                    766.0| 1590.021200626735|1587.0337299986616|0.42110396048628823|0.023059009707359655|  0.1333513889255957|1.4240168706897943E9|  0.1828492671776254|  51.7909829704862|  51.2160368938616|       34.60999999999342| 0.12360637043529334|1587.8881807180298|1587.5488107685205| 51.38818083882827| 0.04518598810631182|1585.7551608093222|                  51295.5|1587.7972255837933| 51.79274715544397| 41.21381507022425|51.788739949990934| 1587.754858685636|       119.69999999998569|                       3.0| 1587.552765320451| 2103.589999999876|                 10.25|  1.859378781766871| 0.35625924431894607|   -6.739999999999756|1587.7401894560496| 49.72602358398766|   5.370000000000197|0.005904407062116368|1587.9246201425278|  6.879999999999271|1587.8881807180298|0.011816810643922928|1479.1233024068845| 0.04377308750693913|1588.8351216888473| 51.79274715544397|
|     ACC|2015-03-01|                   6.5| 49.26022045204308|              230638.0| -2.8599664582717432| 1607.493176269166|1481.6999999999937| 49.45097333766073|20.107719707337395| -51.84631725334683|1607.4931658478522|-0.11862338746655288|1607.5820377400717|1607.5359058034214|1607.4921798563203|1607.4641189255476|1607.5830542584379|             729000.0|  49.4612167279632|               7797.0|                1.1234571E7|1607.4470481351766|-0.08631317568049836| -1.2101921282049783|-0.26939109721601917|108.59999999998657| 1607.411927447776|      5464.390000000426| 21.32974276022784|1607.4470878096097|Construction Mate...|1607.3934196814046|  109.90000000001356|-0.00750501397769...| 28.06320578513418|-3.9322704019188452|              1644761.0|        null| 99.80000000000372| 1.7998194401569139|                    776.0|1609.6666933640868|1606.6252568110745| 0.4323121683588249|-0.02070900759244595|-0.13140522239365465| 1.426726133514962E9|-0.18334822093196373| 48.19587039651099| 49.38956894040469|       34.60999999999421|-0.12173804124953139|1607.4471147833701| 1607.513997478999| 49.20891400913171|-0.04509204671315628|1605.2275362026492|                  51295.5| 1607.538175276659| 48.19506185678671| 39.73838296008299| 48.19867963414227|1607.5784930320058|         120.199999999989|         2.399999999999745|1607.6570956204644|2103.5899999998787|                 10.25|  1.798987643293147| -0.3529477445288059|   -11.40000000000004|1607.6378757152445| 48.77194431854942|   5.254000000000607|-0.00540608338244...| 1607.410495757038|  6.139999999999562|1607.4471147833701|-0.01077843245633034| 835.1360726514813|-0.04258722857502069|1608.2000297752038| 48.19506185678671|
|     ACC|2015-04-01|                   6.5|49.165088984825665|              217437.0|  -4.717163141905119|1562.5383974628887|1527.6033562702564| 49.96701556540131|22.761689668246436|-51.788364958816985|1562.5385676169037| -0.1239612690578514| 1562.627546692905|1562.5841517839124|1562.5398489241945|1562.7110371633155|1562.6262911234785|             542000.0|49.960885162402924|               7514.0|                1.1234571E7| 1562.496399557727|-0.08875125884670691| -2.1594415982654236| -0.2625052661143098|  108.599999999986|1562.4842274025164|      5464.390000000436|21.614185750228994|1562.4965920333618|Construction Mate...|  1562.51197865469|  110.19999999999004|-0.00846665811432...| 30.37985532023296| -7.215517161280854|               154617.0|        null| 99.80000000000615| 1.9424694415940356|                    751.0|1564.7294651732652|1561.5625052661155| 0.4021367915220439|-0.02286424541595063|-0.13200814967075009| 1.429287499059121E9|-0.17022187894958574|48.231160638320205| 49.27533764552744|       34.60999999999375| -0.1268438151507352|1562.4963684875615|1562.9364669444374| 49.11761080437563|-0.04325689082404319| 1560.263271801844|                  51295.5|1562.5824280297707| 48.22986357345047| 41.76106004606293| 48.22586271149177|1562.6286001830333|       120.69999999998684|        0.6999999999999061|1563.0103126896392|2103.5899999998774|                 10.25| 1.9433295885409507|-0.35611571408510057|  -10.990000000000293| 1562.680559951745| 48.22026794874327|   4.865000000000557|-0.00532738576378714|1562.4630740064679|  5.110000000000419|1562.4963684875615|-0.01086939164072...| 944.0188175818004|-0.04444839911823...| 1563.427102934988| 48.22986357345047|
|     ACC|2015-06-01|                  6.25| 50.06128006567195|              212847.0|-7.75786530244087E-4|1429.0924965656582|            1772.0|47.988246422126444|23.702557066748952| -53.35532370286863|1429.0924406060544|-0.05907494546064...|1429.1366242424435|1429.1138203394376|  1429.09228279734|1429.2044854742376|1429.1368674929893|             512000.0| 47.99632723020713|               7514.0|                1.1234571E7|  1429.07022830244|-0.04267913256888...| -0.7005825671741872|-0.13282424242424207|108.59999999998473|1429.0738787878784|      5464.390000000458| 20.28382016989599| 1429.070247298425|Construction Mate...|1429.0462727272716|  111.80000000001543|-0.00407150819038...| 31.38094241014406| 0.8357921298311575|               430993.0|        null| 99.80000000001168| 1.5126217016854038|                    771.0|1430.8166914929384|1428.3552666666694|0.34080710970597405|-0.01113917075913...|-0.06471463148516812|1.4343669927272727E9|-0.08886060606060607| 46.11344568660276| 48.66066086447552|       34.60999999999269|-0.06029871795062927|1429.0702715151297|1429.2662578887755| 49.98127632091758|-0.02203549891628...|1427.3238515373225|                  51295.5| 1429.114791111119| 46.10906663778272| 42.73896372753478|  46.1090329139597|1429.1349619299078|                    123.0|                       1.0|1429.3638357069417|2103.5899999998737|                  10.0| 1.5101878787878926|-0.17333939393939424|  -10.830000000001412|1429.1331846194385| 47.09547363258592|   5.398000000000537|-0.00282347749615...|1429.0525999999963|  5.480000000000606|1429.0702715151297|-0.00568135229469...| 749.5726060606061|-0.02114159046882...|1429.8137696969682| 46.10906663778272|
|     ACC|2015-11-01|                  5.75|  49.6973343879359|              228267.0| -1.3283054924466138| 1358.833029423867|            1772.0|49.597447397153616|23.574440858798727|  -51.8408827313687|1358.8333585370108|-0.04416919149467...|1358.8648953754487|1358.8494992079802| 1358.833937012956| 1358.890330580212| 1358.864555445262|             816000.0| 49.59326975136052|               7263.0|                1.1234571E7|1358.8183704712112|-0.03131466133702789|-0.44825301592624606|-0.09223668014770532|108.59999999998834|1358.8143221382084|      5464.390000000396|21.233387765941437| 1358.818878091093|Construction Mate...| 1358.827650782485|   109.9000000000118|-0.00325060486522...|31.963244821006764|-1.8921506604650362|              1142307.0|        null| 99.79999999999596|  1.214172333493394|                    756.0| 1360.207310256967|1358.2256901705546| 0.3148661440308871|-0.00848063450218...|-0.04637358319764...|1.4475296107227008E9|-0.05960963601195714|49.100664550345535| 48.48326063588668|       34.60999999999569|-0.04434985548089865|1358.8184121680852|1358.8941399064222|49.666260100339265|-0.01505892186062...|1357.4295140791935|                  51295.5|1358.8490481214476| 49.09851793156933| 44.14007261180953| 49.08991943512114|1358.8652516742977|        126.5999999999867|        0.8000000000000854|1358.9134984691475|2103.5899999998837|     9.699999999999124|  1.210858097415161|-0.12310532794091794|   -9.779999999999264|1358.8360764114282| 49.39287127639707|   5.412000000000216|-0.00191690917479...|1358.8077633198518| 6.0699999999997525|1358.8184121680852|-0.00409852301958...| 561.7714084754704|-0.01575246631427...|1359.4095129242182| 49.09851793156933|
|     ACC|2016-09-01|                   6.0| 49.47981740709897|              268058.0|  -3.355108467975603| 1640.078506222307|            1772.0| 49.32462591776426|22.145909080051588| -49.24277370804279|1640.0787773333143|-0.10157107240626705|  1640.15184600001|1640.1154056167984| 1640.079088598737|1640.0507933909203|1640.1518331513332|             608000.0| 49.33331776178084|               7863.0|                1.2163619E7|1640.0425680102508|-0.07260925776571409|  -2.392483115568347| -0.2167933333333333|             104.0|1639.9916177777811|      5851.390000000454|20.553903875878117|1640.0429674306301|Construction Mate...|1640.0316866666642|  111.40000000001469|-0.00615900945273...|30.793453611784884|-3.5730626901496163|              1027728.0|        null|              84.5|   1.52610906918264|                    725.0|1641.7572504287605| 1639.233913333328| 0.3494186581532837|-0.01693074618504...| -0.1087304258575334|        1.47409332E9|-0.14448666666666665| 51.34402173623531| 49.69582880509122|      34.609999999993356|-0.10199867518521963| 1640.042665333331|1640.0675511171312| 49.46088070897399|-0.03612116809181932|1638.3280802379052|        56637.60000000612|1640.1154520000027| 51.33785857627501| 42.90342496766221|  51.3322719745169|1640.1516978565016|       130.90000000001598|                       5.0|1640.0617763538655| 2294.800000000318|     9.699999999998631| 1.5297600000000104|-0.28951333333333285|   -9.069999999999999|1640.1909338193284| 52.61133854675223|   4.389999999999598|-0.00407807269284...|1640.0150599999947| 3.9599999999993774| 1640.042665333331|-0.00842113722681609|          732.1692|-0.03629223969903751|1640.7258800000031| 51.33785857627501|
|     ACC|2017-03-01|                  5.75|49.491400856833565|              262735.0|  -6.629444541114854| 1410.721791313194|            1772.0|49.060357133582286|23.565583461932192|-51.454922759071565|1410.7219072727064|0.030574383107037483| 1410.700015151521|1410.7113191410049|  1410.72224176064|1410.7439316326256|1410.6995673826887|             905000.0|49.067182919961205|               8764.0|                1.2998695E7| 1410.733374102587|0.021781761943754866|      -4.59234230563| 0.06715151515151513| 107.5999999999848|1410.7391656565708|      6182.920000001178|20.635041040625115|1410.7335396026383|Construction Mate...|1410.7478606060608|  113.19999999998419|0.002078388853745374| 32.23952810598773| -7.657719709791194|              1974855.0|        null| 87.69999999999312| 1.0320472959002092|                    744.0|1411.8537990570214|1410.2335636363703|0.31816008085405617|0.006325142549770506|0.033079603943277795|        1.48971012E9| 0.04628484848484847|49.451893775661745|49.284111919455306|       34.60999999999269| 0.02966146079979648|1410.7334206060502|1410.7456338578834| 49.20545245222939| 0.01129784199952293| 1409.613042155074|        64559.39999999029|1410.7106351515226| 49.44191573437821| 44.50498166208425|49.434076438691214|1410.7004599986913|       130.90000000001325|        4.4000000000006825|1410.6888337609898|2651.4699999999466|     9.649999999999817|  1.032400000000012| 0.08652121212121208|  -10.649999999999288| 1410.691392813318|50.377515924215324|   3.889999999999603|0.001746899554882...|   1410.7430969697| 2.0100000000001685|1410.7334206060502|0.003383191010855039| 848.9078787878788| 0.01085914231898228|1411.2408363636387| 49.44191573437821|
|     ACC|2017-06-01|                   6.0|49.664397997228534|              195854.0| -2.2576310265573243|1626.5471609877225|            1772.0| 49.97953134744658| 23.08819348368837| -50.87753707564445| 1626.547142222199|-0.07271798364638145| 1626.597961111132| 1626.572545516046|1626.5469169447508|1626.5317854065313|1626.5981952859747|             663470.0| 49.98822149469257|               7917.0|                1.2998695E7| 1626.521126252042|-0.05149166430889215| -0.7795018736649904|-0.15268148148148147| 107.5999999999866|1626.5025432098819|      6182.920000001042| 20.71329801124263|1626.5212753417566|Construction Mate...|1626.5120000000077|  112.69999999999119|-0.00440990928621...|31.303759621839838|-3.1769200561980067|               650731.0|        null| 87.70000000000195| 1.3259689388497566|                    743.0|1628.0596615848128|1625.8583629629688| 0.3551850404492402|-0.01202844048902788|-0.07713326730659954|        1.49739972E9|-0.10267407407407407| 49.32753433595716| 49.77252162154353|       34.60999999999417|-0.07074349473621051|1626.5212355555545| 1626.587599818823| 49.69214786120583| -0.0256416029977074|1624.9828095262715|        64559.39999999343|1626.5726335802442|49.319115321825684|43.194610807836625| 49.31933322149429|1626.5984086090657|                    132.0|      -0.29999999999996807|1626.6168004403212|   2651.4700000002|     9.599999999998978|   1.32418518518519|-0.20241481481481477|  -12.960000000001036| 1626.586951460973| 49.36888192084301|  1.5400000000001588|-0.00306287615195...|1626.5009851851905| -2.120000000000288|1626.5212355555545|-0.00609729731654...| 714.3631111111112|-0.02586309302077...|1627.1482814814829|49.319115321825684|
|     ACC|2018-04-01|                  5.75| 49.93442879580245|              256459.0|-0.05063271753298533|1556.8622979896174|            1174.0|49.864890981709785|22.186021685536574| -50.74757971748986|1556.8623428571423| 0.06988669062941702|1556.8136298412767| 1556.837438438712| 1556.862154407611|1556.7811345581213| 1556.813788686663|             745051.0| 49.86489098170905|               8821.0|                1.3850857E7|1556.8863459554127|0.049686029270295945| 0.39718565756914465| 0.14623492063492066|110.40000000001501|1556.9202243386246|                 6514.0|21.155120810578104|1556.8865262267739|Construction Mate...|1556.9220190476199|  117.30000000001547|0.004474678770058258|30.852191176984235|-1.1479141551425753|               223417.0|        null|   92.199999999993|  1.333987291408159|                    725.0| 1558.525634790635| 1556.279314285709|0.32689392210756335|0.013204018045723662| 0.07405784844012199|1.5238154914285715E9|  0.0969206349206349|48.607318335040794|48.147867997556205|       34.60999999999301|  0.0691111111517113|1556.8864304762358| 1556.810911429879| 49.85152857969748|0.024371819169826035|1555.2472261618443|        66257.80000000831|1556.8380757672026| 48.60727972158535|43.222185460347546|48.610112629452814|1556.8124683783283|       137.09999999998672|                       4.5|1556.7811995628072| 2701.109999999697|     9.449999999998598| 1.3308888888888994| 0.19629206349206338|  -13.720000000000944|1556.7890667088839| 48.10092011924918|   4.580000000000922|0.003296386054516959|1556.9062730158748|  2.799999999999591|1556.8864304762358|0.006539454253657963| 758.5065396825397|0.024970060379543798|1557.5750857142864| 48.60727972158535|
|     ACC|2018-08-01|                  6.25| 50.38706575458382|              261146.0|  0.2498393992711462|1580.5816133335206|            1174.0| 49.36925044691898|22.181562578629364|-50.434502668160526| 1580.581623492063| 0.09760122653085036|1580.5120482539705|1580.5466354492992|  1580.58148276797|1580.6088913518238|1580.5122933331347|             786003.0| 49.37202475978255|               9208.0|                1.3850857E7|1580.6161374013532| 0.06971864647824513| -1.0009102656017483| 0.20800000000000002|110.40000000001501|1580.6426158730103|                 6514.0| 20.83437128170153|  1580.61618135394|Construction Mate...|1580.6429460317493|  120.09999999998432|0.006204851346505887|30.583089567613605| 0.6295501523768352|              1070859.0|        null|   92.199999999993| 1.5117301990671868|                    700.0| 1582.288240370154| 1579.900692063489|0.40244543105448866|0.018246643646633504| 0.10441723243838501| 1.534434462857143E9| 0.13838095238095238|49.526909214081776| 49.78782767592298|       34.60999999999301| 0.09826393980012844| 1580.616161269909|1580.6309396189783|50.296343873398094| 0.03469858596013988|1578.9440821696412|        66257.80000000831| 1580.547031534389|49.523235459461596|42.657595530343166|49.519384700144435|1580.5117641214968|       140.40000000001075|         4.799999999999943|1580.6751677276893| 2701.109999999697|     9.449999999998598|  1.510488888888888|   0.280025396825396|    -17.9199999999995|1580.5148480309679| 49.57688506037863|   3.689999999999523|0.004506642043117355|1580.6438539682563| 0.2899999999999738| 1580.616161269909|0.009015612296383785| 1685.264126984127| 0.03487132780373331|1581.3833015873058|49.523235459461596|
|     ACC|2019-08-01|     5.149999999999956|49.457850396976774|              178490.0|  -4.856461724383827|1529.6869197907665|            1174.0| 49.72517683981136|21.809467126632327| -50.70549709443406|1529.6868810194226|-0.04239464131136942|  1529.71675137056|1529.7019685773244|1529.6869830017047|1529.7521157520275|1529.7162818732331|             798587.0|  49.7174865359533|               8868.0|                1.4427632E7|1529.6723342759929|-0.03011481491216...| -2.0098898835255117|-0.08938361238702033|123.90000000000612|1529.6669630068634|      6689.369999999999| 21.58637448396144|1529.6721939945362|Construction Mate...|1529.6806341680253|               121.5|-0.00274454828636...| 29.95850718236893| -6.404311067507796|              1175301.0|        null|108.79999999999326| 2.0282634432210096|                    662.0|1531.9543273623872|1528.6733738331545| 0.4624674724980015|-0.00674081080078...|-0.04490382208536666|1.5659967040539339E9|-0.05847532967847093| 49.42792769253955| 49.30386218887929|      25.169999999998605|-0.04241682527407111|1529.6722773744991| 1529.753323433954| 49.44331558143869|-0.01478900717319...| 1527.390227386614|        71468.90000000296|1529.7013493357044| 49.43398325169964| 41.42784297639298| 49.43724927251633|1529.7170978166182|                    145.0|        -1.399999999999996|1529.7796031081425|            2870.5|      9.40000000000057|  2.027596680989785| -0.1213587198103427|  -13.860000000000454| 1529.652170387154| 49.45502558942691|   3.280000000000109|-0.00161784493964...| 1529.660127426287|  2.989999999999828|1529.6722773744991|-0.00319711471179...|2020.7627796710624|-0.01512923929332...|1530.6673877611547| 49.43398325169964|
|     ACC|2020-09-01|     3.349999999999858| 50.41572128680984|              197555.0|  0.6542087166907428|1363.8120794774136|1869.9499999998718| 49.87697226602822|20.395316031989175| -48.82369654753964|1363.8121349019666| 0.07992086991314654|1363.7540141176405|1363.7838137243752| 1363.812695816937|1363.8355660395175|1363.7532492689443|              28167.0| 49.89265854053613|               8520.0|                1.3280223E7|1363.8419415331086| 0.05743583168403611|-0.14292795813351533| 0.17753725490196084|124.80000000001047|1363.8380915032708|      6186.609999999561|20.773441718560715|1363.8422725129099|Construction Mate...|  1363.86470588235|  122.90000000001412|0.005865179849632459|28.161262644249266| 1.8514406922431785|              1479410.0|        null|102.90000000001233|  1.910121871382614|                    618.0|1365.9365780860578|1362.8830666666581|0.45088596498814026|0.018042430857728085| 0.08701252765760371|1.6003605670588236E9| 0.12047843137254904| 51.58869712784897|49.687366413542506|      25.170000000001103| 0.07953313227883779|1363.8420407843855| 1363.931150885012| 50.32643845906312|0.029576695973567598|1361.7475034827073|        72937.10000000765|1363.7825448366098| 51.58068955539104| 39.89901988696126| 51.57474056711988|1363.7552599852552|       156.40000000001606|                       1.0|1363.9634354933419|  2667.68999999985|                   9.0| 1.9097254901960818| 0.22997647058823528|   -2.959999999999521| 1363.706099304432| 52.62732878149021|    7.26999999999933|0.004769407197511656|1363.8667843137257| 10.679999999999996|1363.8420407843855|0.009460053072048316| 2356.270274509804|0.028553739119412627|1364.7644235294215| 51.58068955539104|
|     ACC|2020-11-01|      3.34999999999994| 49.67745341659071|              209476.0| -2.0878145019796452|1687.8596868925567|1869.9499999999105|50.286563004323476| 21.80729228561921|-49.996647020100596| 1687.859513252211|0.062152213127478935|1687.8108143023774| 1687.835151592077| 1687.858558476134|1687.8639382033498|1687.8117066478844|              70977.0| 50.29211948595726|               9245.0|                1.3280223E7|1687.8822070295312|0.045961052419045405| -0.7990055999927165| 0.14099849974995834| 124.8000000000082|1687.9090737345143|      6186.609999999571|21.555727047792512|1687.8817313558761|Construction Mate...|1687.9136856142713|  125.09999999998628|0.004141197735981265|29.651780695642312|-2.7118962332669714|              1906358.0|        null|102.90000000001164| 2.1182326498914246|                    618.0|1690.2102253130715|1686.8634355725915|0.44744196963923116|0.012289414383075915|  0.0691339321581224|1.6054323037306218E9| 0.09069011501916985|  49.6665002878012| 48.15103462756178|       25.17000000000037| 0.06946061529157155|1687.8820720120782| 1687.721264628014| 49.72522627869896|0.023172879739076988|1685.5539187110671|        72937.10000000663|1687.8355570372898|49.669872662020154|41.243396499962074| 49.67211686290141|1687.8125974237123|       158.90000000001575|        -1.600000000000181|1687.6255600996953|2667.6899999998463|     8.800000000000786|  2.126746124354073|  0.1920320053342224|  -10.190000000000774| 1687.822125312153| 48.82972767325069|   6.930000000000255|0.002801203038031...|1687.8983580596769|                9.5|1687.8820720120782|0.005823024498459208| 4996.427737956326|0.022554168353800464|1688.9654275712667|49.669872662020154|
|     ACC|2021-01-01|    3.3499999999999344|50.497384292821565|              190183.0|  2.3612300151144594|1698.1440230068329|1869.9499999999107| 49.86365158212937|20.905459769156547|-49.910049002325124|1698.1441819607887|-0.01288237879627...| 1698.158566274501|  1698.15053777044|1698.1441253493726| 1698.009377443505| 1698.158641996235|              83822.0|49.869009883997826|              10000.0|                1.4536732E7|1698.1367850840345|-0.01149636280124...|  1.7420574880737019|-0.04305098039215...|140.30000000001206| 1698.145369934636|     6675.3500000007225| 21.12688199753174|1698.1371201052452|Construction Mate...|1698.1426509803919|               126.5|-0.00123811324021...|29.517369338765544|  2.965578374620014|              2517318.0|        null|             105.5|  2.039335590841497|        619.1499999999406|1700.3974848914368|1697.1499294117627| 0.4520843880788953|-0.00241695057000...|-0.01850160692868742|1.6107031552941177E9|-0.02789019607843...| 49.53953532222643| 50.62859537470962|       25.17000000000044|-0.02008039209602...| 1698.136854902029|1697.7925522853618|50.515855807183804|-0.00700524412743...| 1695.876224912606|                  76598.0|1698.1514420915012| 49.53642957873037|  41.6356557089261|49.532412882229316|1698.1556217121738|       156.30000000001536|       -0.5999999999999422| 1697.701725125349| 3173.400000000043|     8.800000000000761|  2.042933333333346|-0.05642352941176539|   -14.49000000000077|1698.2087023742165| 48.55272377319887|   4.060000000000305|-5.29988587315213...|1698.1319843137276| 1.8899999999999408| 1698.136854902029|-0.00117525332895...|3264.5592156862745|-0.00508394173518...|  1699.15419607843| 49.53642957873037|
|     ACC|2021-02-01|    3.3500000000002212| 50.05259567742876|              190484.0|  -3.202449110511591|1758.4652789877744|1869.9500000001099| 49.83255506851524|20.911455117813286| -50.10359802724794| 1758.465274224288| 0.12221822747282025| 1758.379644801297|1758.4209001415181|1758.4639922613558|1758.5723327500243|1758.3814236401097|              99640.0|49.833202254531415|               9100.0|                1.4536732E7| 1758.505833891437| 0.08678272752120088|  -2.210629071998468|  0.2527830702231895|140.29999999999836| 1758.535687715482|     6675.3500000002905| 21.06889906170169|1758.5056624992853|Construction Mate...|1758.5222645617835|  128.09999999999198|0.007449026478986...|29.193645521174712|-3.6180213786840807|              2818643.0|        null|             105.5|   2.55647109476426|        618.5900000000031|1761.3185886268896|1757.2707947740837|  0.467596116807699|0.022067180525954033| 0.12845296544906903| 1.613243363565596E9|  0.1617378878606424| 49.63274565878134| 48.89968676946759|      25.169999999998574| 0.12376422859675727|  1758.50582335335| 1758.507171045154| 49.91646020639196| 0.04167023792786816|1755.6930580798173|                  76598.0|1758.4236885320295| 49.63662512220892| 40.82458109625625| 49.63808691641429|1758.3772095338306|        156.5999999999892|        -3.200000000000131|1758.5790583630821|3173.4000000002147|     8.799999999999905|  2.558662221012528| 0.34760479041916326|  -12.620000000000532|1758.4015458263584| 49.72950847430849|   5.030000000000006| 0.00517266674686123|1758.5379286880782|  3.870000000000204|  1758.50582335335| 0.01034843245822826|3420.7524496461624| 0.04369060768542613|1759.7983396842747| 49.63662512220892|
|     ACC|2022-01-01|     3.349999999999661|50.561670738080096|              162601.0|   2.440858571278567| 2257.002591333603| 895.1500000000988| 49.13409609805948|20.671767513836123|-48.629553145423294|2257.0024526666684| 0.06074588307913758| 2256.960738333323|2256.9810008073405|2257.0020449658814|2256.9441612461883| 2256.960951029923|             201546.0| 49.13795935590224|              10800.0|                 1.513278E7| 2257.022646325125| 0.04261188014672165|-0.36329890229128053| 0.12371999999999989|143.80000000001993|2257.0035422222268|                 6700.0| 21.41246233385628| 2257.022557127831|Construction Mate...| 2257.041186666665|  143.80000000001993|0.002618630560759795|29.156921050018212|  3.374233664143609|              2809384.0|        null|133.69999999998208| 2.6808472709738886|        604.2000000000849| 2259.795033640589| 2255.662126666683| 0.4116002691765086| 0.00822941755657787| 0.06312404210114134|        1.64231412E9| 0.08149999999999988| 51.30137779267431| 50.75449036391336|      34.940000000001966| 0.05867435903773312|2257.0226306666536| 2256.943533041759| 50.49820189882675|0.020512161954419694| 2254.250227692723|                  80000.0|2256.9816279999955| 51.30274513187338|41.242767909270256| 51.30252099571606|2256.9594330857267|       165.69999999997657|                       2.0|2256.8304530529726|            3469.0|      8.80000000000121| 2.6805466666666518| 0.16564666666666658|   -17.41999999999848|2257.0127069374016| 52.24870892242551|   6.010000000000043|0.001960677962589...|2257.0384466666665|  5.430000000000254|2257.0226306666536|0.004041790622388502|1049.6338666666666|0.021567721600874695| 2258.310053333323| 51.30274513187338|
|     ACC|2022-09-01|    3.3499999999995596|50.198970446950575|              186867.0|  -0.802952065717992|2473.0404393942063| 895.1500000001237|49.436307193751304|21.199887338766278|-51.126433268831825| 2473.040543636374| 0.09869544649446305|2472.9683663636397|  2473.00526656844|2473.0410293859736| 2473.185949588434|2472.9678844624896|             606891.0| 49.43391051385238|               9900.0|                 1.513278E7| 2473.077374401596| 0.07106930342244562| -0.7290161999279634| 0.21855151515151452|143.80000000002235|2473.0876000000017|                 6700.0| 21.20956231272943| 2473.077449751784|Construction Mate...|2473.0928666666664|   152.1000000000016| 0.00423547060238682| 28.83992703670157|-0.5278288941444729|              1823597.0|        null| 133.6999999999795| 4.5341610044734955|        685.9800000001029|2477.9756962994006|2470.8361272727543|0.48921491903429154|0.013980856816909623| 0.10748966923738705|1.6633348472727273E9| 0.14650909090909017| 49.30751078128637| 50.57262653679213|      34.940000000001994| 0.09946526813022424| 2473.077347878796| 2473.210722704382| 50.11765454971323| 0.03642036581494143|2468.1789994581723|                  80000.0| 2473.004157575708| 49.30624104725494| 40.51449704173318| 49.30060806679288| 2472.969960082543|        175.3000000000267|        3.0999999999996417|2473.3346134891467|            3469.0|     8.800000000001365|  4.536921212121182|  0.2861575757575757|   -25.70999999999863|2472.8681209193173| 50.01751418956178|   7.410000000001548|0.003637557445317303|2473.1070060606075|  8.599999999999495| 2473.077347878796|0.007152304042787357| 4826.811636363636|0.035306485898695346| 2475.319666666644| 49.30624104725494|
|ADANIENT|2015-05-01|                   6.5|50.704353904453214|              219798.0|  1.7418483674718064| 741.9407728104702| 590.6549176470585| 50.17968063278244|22.788314258686363| -47.59728243993599|  741.940436862746| 0.03293733262936284|   741.91175764706|  741.926899726394| 741.9400828482698| 741.9537801402535| 741.9119280568515|             509000.0|50.169422104120265|               7708.0|                1.1234571E7| 741.9541020078095|0.025285995096065224|  1.4598326543065452| 0.08397647058823615|108.59999999998709| 741.9585516339841|      5464.390000000417| 21.16407411757601| 741.9536485875716|     Metals & Mining| 741.9671372549026|  111.40000000001316|0.005715588818220529|29.784021565656676| 3.0542508481467134|               262831.0|        null| 99.80000000000142| 1.1745812208130089|                    766.0| 743.2113411449169| 741.3828705882312|0.48867998438314963|0.018057742108649046|0.038851734398854844|1.4319415552941177E9| 0.05457254901960731| 52.13471246219177|51.009999160079225|       34.60999999999465| 0.03937828054692422| 741.9540188235299| 741.8902024225351| 50.63325831153231|0.013565739302789624| 740.6966965021393|                  51295.5| 741.9260538562105|  52.1451068426361| 40.79242417991894| 52.15711235868314| 741.9147968531719|       121.59999999998598|        1.7999999999998837| 741.8424155337871|  2103.58999999988|                  10.0| 1.1749254901960802| 0.11051764705882366|    -10.4100000000017| 741.9396474846357| 51.29859413030272|   5.008999999999129|0.004140253308034688| 741.9635450980405| 4.7999999999995016| 741.9540188235299|0.008916785011885527| 7641.484862745098|0.012102873224330877| 742.5292392156903|  52.1451068426361|
|ADANIENT|2015-07-01|                  6.25|49.336555024531044|              229414.0|  -6.405990381373355| 87.48794290290027| 803.9000000000675| 49.68354274987704| 22.27000656052686|-52.479843133958774| 87.48793489861272|-0.00348003969334...| 87.49048926093948| 87.48916124455387| 87.48788953111803|  87.5082069415727|  87.4905394995302|             628000.0| 49.68842891870525|               7668.0|                1.1234571E7| 87.48658104456273|-0.00251841472342...|  -3.586757935269739|-0.00783084311632...|108.59999999998554| 87.48610814656706|      5464.390000000444| 20.00714163012356| 87.48657060081415|     Metals & Mining| 87.48523212379922|  111.09999999998534|-0.00388424578980...|30.155370471040268| -7.216921032584023|               600979.0|        null| 99.80000000000817|0.14656362527199596|                    754.0| 87.62866487575235| 87.41443436499438| 0.2945924045602651|-0.01023864992063...|-0.00383734502731...|1.4371096500800426E9|-0.00537620064034...| 48.27333048684419|47.179869172278885|       34.60999999999336|-0.00346496763484...| 87.48658217716088| 87.52749857461389|49.344017292098414|-0.00131893030388...| 87.34449947856827|                  51295.5|  87.4892431519032| 48.27421985006039|41.616470489019044| 48.27577623568775|  87.4904079458411|       123.59999999998446|        1.2999999999999918| 87.54162193482804| 2103.589999999876|                  10.0|0.14673159018143025|-0.01004535752401...|  -12.809999999998661| 87.48779585623599|49.250489807655896|   3.690999999999718|-0.00278709101060...| 87.48547892209139|  2.149999999999745| 87.48658217716088|-0.00550110434500...| 9651.129935965848|-0.00124670128762...| 87.55841115261474| 48.27421985006039|
|ADANIENT|2016-01-01|                  5.75|49.180864004478714|              215995.0|  -3.712777634597596| 78.08159267976092| 803.9000000000138|49.876377061984655|24.210153227142957| -50.39046819190358| 78.08158509803955|-0.01168271232583...| 78.08985647058863| 78.08574441147813|  78.0815961491732| 78.07682850238454| 78.08984648773638|             845000.0| 49.86546433076319|               7501.0|                1.2163619E7|  78.0774876639353|-0.00831543723436...| -2.7950491270643143|-0.02483137254901...|             104.0| 78.07359738562117|      5851.390000000429|21.077972860504932| 78.07746076788818|     Metals & Mining|  78.0774509803923|               108.0|-0.01484360449424...|31.242945918589957| -5.357715467238324|              1436340.0|        null|              84.5|0.20264813787732647|                    730.0| 78.30277833173713| 77.97503529411719| 0.3502525529527759|-0.03753392736382352|-0.01245081851940...|1.4528706847058823E9|-0.01652549019607...| 50.17915367584198| 48.45207448867783|       34.60999999999465|-0.01160708898421...| 78.07747607843326| 78.10479758606562| 49.12794060924538|-0.00413538128504...|  77.8521738251291|        56637.60000000299| 78.08571921568623| 50.18583486683896|42.024362746215544|50.186839120391966| 78.08991158640755|       126.30000000001088|                       4.0| 78.11156112803293|2294.8000000002426|     9.699999999998905|0.20263529411764533|-0.03319215686274505|   -7.669999999998862| 78.09287364675433| 50.84834071225887|   5.689999999999709|-0.00936066217697...| 78.07410980392167|  6.849999999999187| 78.07747607843326|-0.01874007622952373|10427.515294117647|-0.00416717492936...|  78.1716470588238| 50.18583486683896|
+--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+
only showing top 20 rows
In [ ]:
monthly_df = monthly_df.toPandas()
In [ ]:
monthly_df
Out[ ]:
company month avg(Reverse Repo Rate) avg(RSI14) avg(Car Registrations) avg(CCI10) avg(TRIMA10) avg(SAR) avg(fastdsr) avg(ADX20) ... avg(Inflation Rate ) avg(ROC5) avg(close) avg(Food Inflation) avg(middleband) avg(ROC10) avg(volume) avg(macd1520) avg(high) avg(slowk)
0 ACC 2015-02-01 6.75 51.341733 225854.0 7.375884 1587.842455 1510.245415 50.706838 20.462642 ... 5.370 0.005904 1587.924620 6.88 1587.888181 0.011817 1479.123302 0.043773 1588.835122 51.792747
1 ACC 2015-03-01 6.50 49.260220 230638.0 -2.859966 1607.493176 1481.700000 49.450973 20.107720 ... 5.254 -0.005406 1607.410496 6.14 1607.447115 -0.010778 835.136073 -0.042587 1608.200030 48.195062
2 ACC 2015-04-01 6.50 49.165089 217437.0 -4.717163 1562.538397 1527.603356 49.967016 22.761690 ... 4.865 -0.005327 1562.463074 5.11 1562.496368 -0.010869 944.018818 -0.044448 1563.427103 48.229864
3 ACC 2015-06-01 6.25 50.061280 212847.0 -0.000776 1429.092497 1772.000000 47.988246 23.702557 ... 5.398 -0.002823 1429.052600 5.48 1429.070272 -0.005681 749.572606 -0.021142 1429.813770 46.109067
4 ACC 2015-11-01 5.75 49.697334 228267.0 -1.328305 1358.833029 1772.000000 49.597447 23.574441 ... 5.412 -0.001917 1358.807763 6.07 1358.818412 -0.004099 561.771408 -0.015752 1359.409513 49.098518
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
8888 UPL 2022-04-01 3.35 50.282861 153995.0 -0.097747 808.763722 239.400000 49.774696 20.623994 ... 7.790 0.005578 808.802304 8.38 808.784862 0.010913 7372.943852 0.019620 809.220267 51.261911
8889 VEDL 2016-11-01 5.75 50.778532 235977.0 0.135301 217.227011 120.392108 49.856638 22.728598 ... 3.630 0.008993 217.242660 2.03 217.235665 0.017875 45558.532825 0.008557 217.445962 50.753514
8890 VEDL 2021-12-01 3.35 49.799167 159360.0 -1.850097 343.598404 58.250000 49.276777 22.684132 ... 5.660 0.001069 343.600456 4.05 343.599599 0.002018 24355.279197 0.000605 343.820733 49.835236
8891 WIPRO 2021-02-01 3.35 48.974485 190484.0 -6.424699 430.330181 153.420000 49.850835 21.081574 ... 5.030 -0.001331 430.324439 3.87 430.327254 -0.002295 30572.976459 -0.002715 430.588386 50.576229
8892 WIPRO 2022-06-01 3.35 48.766103 179880.0 -4.586601 445.847586 153.420000 48.893408 22.128834 ... 7.010 -0.009633 445.807537 7.75 445.825520 -0.019279 19548.810667 -0.021775 446.039993 49.418171

8893 rows × 84 columns

In [ ]:
monthly_df.columns
Out[ ]:
Index(['company', 'month', 'avg(Reverse Repo Rate)', 'avg(RSI14)',
       'avg(Car Registrations)', 'avg(CCI10)', 'avg(TRIMA10)', 'avg(SAR)',
       'avg(fastdsr)', 'avg(ADX20)', 'avg(WILLR)', 'avg(sma10)',
       'avg(macd1226)', 'avg(sma20)', 'avg(ema15)', 'avg(ema10)',
       'avg(KAMA10)', 'avg(TRIMA20)', 'avg(Tourist Arrivals)', 'avg(fastksr)',
       'avg(Steel Production)', 'avg(Gross National Product)', 'avg(TRIMA5)',
       'avg(macd1020)', 'avg(CCI5)', 'avg(MOM15)', 'avg(Export Prices)',
       'avg(TYPPRICE)', 'avg(GDP per Capita PPP)', 'avg(HT_DCPERIOD)',
       'avg(ema5)', 'first(sector)', 'avg(open)', 'avg(Producer Prices)',
       'avg(PPO)', 'avg(ADX10)', 'avg(CCI15)', 'avg(Fiscal Expenditure)',
       'avg(company)', 'avg(Import Prices)', 'avg(ATR)',
       'avg(Crude Oil Production)', 'avg(upperband)', 'avg(low)', 'avg(BETA)',
       'avg(ROC20)', 'avg(macd520)', 'avg(date)', 'avg(MOM10)', 'avg(fastk)',
       'avg(MFI)', 'avg(Corporate Tax Rate )', 'avg(APO)', 'avg(sma5)',
       'avg(KAMA20)', 'avg(RSI8)', 'avg(macd510)', 'avg(lowerband)',
       'avg(Military Expenditure)', 'avg(sma15)', 'avg(fastd)', 'avg(ADX5)',
       'avg(slowd)', 'avg(ema20)', 'avg(Consumer Price Index)',
       'avg(Industrial Production)', 'avg(KAMA30)', 'avg(GDP)',
       'avg(Bank Lending Rate)', 'avg(Trange)', 'avg(MOM20)',
       'avg(Balance of Trade)', 'avg(HT_TRENDLINE)', 'avg(ULTOSC)',
       'avg(Inflation Rate )', 'avg(ROC5)', 'avg(close)',
       'avg(Food Inflation)', 'avg(middleband)', 'avg(ROC10)', 'avg(volume)',
       'avg(macd1520)', 'avg(high)', 'avg(slowk)'],
      dtype='object')
In [ ]:
# Plot for ADANIGREEN
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
adanigreen = monthly_df[monthly_df['company'] == 'ADANIGREEN'].set_index('month').sort_index()
ax1[0].plot(adanigreen['avg(close)'])
ax1[0].set_title('ADANIGREEN Close', fontstyle='italic')

ax1[1].plot(adanigreen['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')

ax2[0].plot(adanigreen['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')

ax2[1].plot(adanigreen['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')

ax3[0].plot(adanigreen['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')

ax3[1].plot(adanigreen['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')

ax4[0].plot(adanigreen['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')

ax4[1].plot(adanigreen['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')

fig.tight_layout()
In [ ]:
# Plot for ADANIENT
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
adanient = monthly_df[monthly_df['company'] == 'ADANIENT'].set_index('month').sort_index()
ax1[0].plot(adanient['avg(close)'])
ax1[0].set_title('ADANIENT Close', fontstyle='italic')

ax1[1].plot(adanient['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')

ax2[0].plot(adanient['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')

ax2[1].plot(adanient['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')

ax3[0].plot(adanient['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')

ax3[1].plot(adanient['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')

ax4[0].plot(adanient['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')

ax4[1].plot(adanient['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')

fig.tight_layout()
In [ ]:
# Plot for BAJAJFINANCE
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
bajfin = monthly_df[monthly_df['company'] == 'BAJFINANCE'].set_index('month').sort_index()
ax1[0].plot(bajfin['avg(close)'])
ax1[0].set_title('BAJFINANCE Close', fontstyle='italic')

ax1[1].plot(bajfin['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')

ax2[0].plot(bajfin['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')

ax2[1].plot(bajfin['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')

ax3[0].plot(bajfin['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')

ax3[1].plot(bajfin['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')

ax4[0].plot(bajfin['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')

ax4[1].plot(bajfin['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')

fig.tight_layout()
In [ ]:
# Plot for RELIANCE
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
reliance = monthly_df[monthly_df['company'] == 'RELIANCE'].set_index('month').sort_index()
ax1[0].plot(reliance['avg(close)'])
ax1[0].set_title('RELIANCE Close', fontstyle='italic')

ax1[1].plot(reliance['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')

ax2[0].plot(reliance['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')

ax2[1].plot(reliance['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')

ax3[0].plot(reliance['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')

ax3[1].plot(reliance['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')

ax4[0].plot(reliance['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')

ax4[1].plot(reliance['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')

fig.tight_layout()
In [ ]:
indicator_columns = ['Inflation Rate ', 'Balance of Trade', 'Bank Lending Rate', 'Car Registrations', 'Consumer Price Index', 'Crude Oil Production', 'Fiscal Expenditure', 'Industrial Production', 'Food Inflation', 'Producer Prices', 'Reverse Repo Rate', 'Steel Production', 'Tourist Arrivals', 'Corporate Tax Rate ', 'Export Prices', 'GDP per Capita PPP', 'GDP', 'Gross National Product', 'Import Prices', 'Military Expenditure']
In [ ]:
close_plus_indicators = []
for col in (['close'] + indicator_columns):
    close_plus_indicators.append('avg({})'.format(col))
adanient_essential = adanient[close_plus_indicators]
adanigreen_essential = adanigreen[close_plus_indicators]
reliance_essential = reliance[close_plus_indicators]
bajfin_essential = bajfin[close_plus_indicators]
In [ ]:
plt.figure(figsize=(12,12))
sns.heatmap(adanient_essential.corr(), cmap='coolwarm', annot=True)
Out[ ]:
<AxesSubplot:>
In [ ]:
plt.figure(figsize=(12,12))
sns.heatmap(adanigreen_essential.corr(), cmap='coolwarm', annot=True)
Out[ ]:
<AxesSubplot:>
In [ ]:
plt.figure(figsize=(12,12))
sns.heatmap(bajfin_essential.corr(), cmap='coolwarm', annot=True)
Out[ ]:
<AxesSubplot:>
In [ ]:
plt.figure(figsize=(12,12))
sns.heatmap(reliance_essential.corr(), cmap='coolwarm', annot=True)
Out[ ]:
<AxesSubplot:>
In [ ]:
exprs = {x: "avg" for x in cols}
for indicator in indicator_columns:
    exprs[indicator] = 'first'
exprs["company"] = "first"
sector_df = df.groupby(df.sector, df.month).agg(exprs).toPandas()
In [28]:
sector_df
Out[28]:
sector month first(Reverse Repo Rate) avg(RSI14) first(Car Registrations) avg(CCI10) avg(TRIMA10) avg(SAR) avg(fastdsr) avg(ADX20) ... first(Inflation Rate ) avg(ROC5) avg(close) first(Food Inflation) avg(middleband) avg(ROC10) avg(volume) avg(macd1520) avg(high) avg(slowk)
0 Automobile and Auto Components 2015-08-01 6.25 49.662731 224700 -1.998022 5337.720340 5529.659650 50.003184 21.683560 ... 3.741 -0.004738 5337.516660 2.20 5337.607947 -0.009484 4880.420590 -0.102422 5340.834713 49.984793
1 Automobile and Auto Components 2015-12-01 5.75 49.945582 236761 -0.504177 4401.618016 5554.567799 49.985572 22.463151 ... 5.611 -0.000729 4401.618949 6.40 4401.620745 -0.001406 3841.525978 -0.000174 4403.275623 50.178142
2 Automobile and Auto Components 2017-04-01 6.00 50.197289 254290 -0.486540 5541.569830 5414.435714 49.927982 23.755683 ... 2.990 0.002254 5541.670408 0.61 5541.626588 0.004538 3677.465905 0.057779 5543.670202 50.286948
3 Automobile and Auto Components 2018-03-01 5.75 49.606189 263487 -1.560610 5273.128254 5414.435714 50.170437 23.622784 ... 4.280 -0.002404 5273.045940 2.81 5273.079706 -0.004956 4925.275733 -0.059460 5275.285472 49.709954
4 Automobile and Auto Components 2018-04-01 5.75 50.389887 256459 -0.216702 5632.311644 5414.435714 49.904657 23.615024 ... 4.580 0.004572 5632.477104 2.80 5632.405683 0.009271 5768.151147 0.099991 5634.661498 50.852952
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1764 Services 2021-08-01 3.35 50.593187 151757 1.040625 1187.519193 426.347561 48.673977 21.311919 ... 5.300 0.008594 1187.616254 3.11 1187.573210 0.017200 13620.365659 0.051716 1188.353961 48.792737
1765 Telecommunication 2019-04-01 5.75 49.604986 218679 -0.497951 319.180610 518.425000 51.265332 22.292561 ... 2.990 -0.007952 319.158373 1.10 319.168168 -0.016025 18371.854275 -0.012474 319.378039 51.108816
1766 Telecommunication 2019-11-01 4.90 50.895531 210687 1.773356 311.905260 518.425000 48.967278 22.526909 ... 5.540 0.018855 311.950690 10.01 311.930547 0.038016 54653.968067 0.025804 312.218040 50.508269
1767 Telecommunication 2020-08-01 3.35 49.600241 157761 -3.845947 361.803809 385.775000 49.175487 22.741805 ... 6.690 -0.001361 361.793421 9.05 361.798031 -0.002578 42685.908421 -0.005396 362.007100 48.872732
1768 Telecommunication 2021-03-01 3.35 49.213446 194780 -3.307220 386.009998 385.775000 49.544487 22.111032 ... 5.520 -0.003927 385.993700 4.94 386.000968 -0.007858 35912.109185 -0.009589 386.258626 49.265391

1769 rows × 84 columns

In [32]:
indicators2 = []
for col in indicator_columns:
    indicators2.append('first({})'.format(col))
In [58]:
fig, ax = plt.subplots(7, 3)
fig.set_figheight(20)
fig.set_figwidth(15)

sectors = list(sector_df['sector'].unique())

num_plots = 0
for i in range(13):
    for j in range(3):
        try:
            sector = sector_df[sector_df['sector'] == sectors[3*i + j]].set_index('month').sort_index()
            ax[i][j].plot(sector['avg(close)'])
            ax[i][j].set_title(sectors[3*i + j], fontstyle='italic')
        except IndexError:
            pass
plt.tight_layout()
In [55]:
# Indicators Plot
fig, ax = plt.subplots(7, 3)
fig.set_figheight(20)
fig.set_figwidth(15)

sector = sector_df[sector_df['sector'] == 'Power'].set_index('month').sort_index()
for i in range(7):
    for j in range(3):
        try:
            ax[i][j].plot(sector[indicators2[3*i+j]])
            ax[i][j].set_title(indicator_columns[3*i+j], fontstyle='italic')
        except IndexError:
            pass
            
plt.tight_layout()
In [46]:
sectors
Out[46]:
['Automobile and Auto Components',
 'Capital Goods',
 'Chemicals',
 'Construction',
 'Construction Materials',
 'Consumer Durables',
 'Consumer Services',
 'Fast Moving Consumer Goods',
 'Financial Services',
 'Healthcare',
 'Information Technology',
 'Metals & Mining',
 'NIFTY 50',
 'NIFTY BANK',
 'Oil Gas & Consumable Fuels',
 'Power',
 'Realty',
 'Services',
 'Telecommunication']
In [45]:
# Power Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Power'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
In [47]:
# Financial Services Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Financial Services'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
In [48]:
# Oil Gas & Consumable Fuels Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Oil Gas & Consumable Fuels'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
In [49]:
# Services Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Services'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
In [ ]: